package com.ysd.Dao.Impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.Banner;
import com.ysd.entity.Design;
import com.ysd.entity.News;
import com.ysd.util.DBmanager;

public class DesignDaoImpl {


	/**
	 * 分页查询所有设计师
	 * 		 
	 * @return
	 */
	public List<Design> getDesignByPage(int page,int size) {
			List<Design> list = new ArrayList<Design>();
			//1建立连接
			Connection conn = DBmanager.getConnection();
			Statement st = null;
			ResultSet rs = null;
			try {//try正常执行
				//2创建statement执行对象
				st = conn.createStatement();
				//3SQL语句
				String sql = "select * from design order by designId desc limit "+page+","+size;
				//4执行SQL语句
				rs = st.executeQuery(sql);
				//5便利结果集
				while(rs.next()) {
					
					Integer designId = rs.getInt("designId");
					String designTitle = rs.getString("designTitle");
					String designName = rs.getString("designName");
					String designGrade = rs.getString("designGrade");
					Design design = new Design(designId,designTitle,designName,designGrade);
					list.add(design);
				}
				return list;//正常情况下返回集合
			} catch (SQLException e) {//catch异常执行
				e.printStackTrace();
				return null;//异常情况下返回空
			} finally {
				DBmanager.closeAll(conn, st, rs);
			}
		}
	
	/**
	 * 查询所有设计师集合
	 * 		 
	 * @return
	 */
	public List<Design> getDesign() {
			List<Design> list = new ArrayList<Design>();
			//1建立连接
			Connection conn = DBmanager.getConnection();
			Statement st = null;
			ResultSet rs = null;
			try {//try正常执行
				//2创建statement执行对象
				st = conn.createStatement();
				//3SQL语句
				String sql = "select * from design";
				//4执行SQL语句
				rs = st.executeQuery(sql);
				//5便利结果集
				while(rs.next()) {
					
					Integer designId = rs.getInt("designId");
					String designTitle = rs.getString("designTitle");
					String designName = rs.getString("designName");
					String designGrade = rs.getString("designGrade");
					Design design = new Design(designId,designTitle,designName,designGrade);
					list.add(design);
				}
				return list;//正常情况下返回集合
			} catch (SQLException e) {//catch异常执行
				e.printStackTrace();
				return null;//异常情况下返回空
			} finally {
				DBmanager.closeAll(conn, st, rs);
			}
		}
		/**
		 * 
		 * 根据设计师id删除设计师
		 * dId前台传来的参数
		 * @author 86176
		 *return 返回受影响行数
		 */
		public int deleteDesignById(int dId) {
			return DBmanager.executeUpdate("DELETE FROM design where designId ="+dId);
		}
		
		/**
		 * 
		 * 根据设计师id批量删除设计师
		 * dId前台传来的参数
		 * @author 86176
		 *return 返回受影响行数
		 */
		public int deleteDesignByIds(String dIds) {
			String sql ="DELETE FROM design where designId in("+dIds+")";
			System.out.println(sql);
			return DBmanager.executeUpdate(sql);
		}
		/**
		   *添加设计师
		 * de 前台传来的轮播数据
		 *return 受影响行数
		 */
		public int addDesign(Design de) {
			return DBmanager.executeUpdate("insert into design VALUES(null,'"+de.getDesignTitle()+"','"+de.getDesignName()+"','"+de.getDesignGrade()+"')");
		}
		/**
		 * 
		 * 根据轮播id查询设计师
		 * dId前台传来的参数
		 * @author 86176
		 */
		public Design getDesignById(int dId) {
			//1建立连接
			Connection conn = DBmanager.getConnection();
			Statement st = null;
			ResultSet rs = null;
			Design design = null;
			try {//try正常执行
				//2创建statement执行对象
				st = conn.createStatement();
				//3SQL语句
				String sql = "select * from design where designId="+dId;
				//4执行SQL语句
				rs = st.executeQuery(sql);
				//5便利结果集
				while(rs.next()) {
					Integer designId = rs.getInt("designId");
					String designTitle = rs.getString("designTitle");
					String designName = rs.getString("designName");
					String designGrade = rs.getString("designGrade");
					 design = new Design(designId,designTitle,designName,designGrade);
				}
				return design;//正常情况下返回集合
			} catch (SQLException e) {//catch异常执行
				e.printStackTrace();
				return null;//异常情况下返回空
			} finally {
				DBmanager.closeAll(conn, st, rs);
			}
		}
		
		
		
		/**
		 *编辑设计师
		 * de 前台传来产品分类数据
		 *return 受影响行数
		 */
		public int editDesign(Design de) {
			String sql = "update design set designTitle = '"+de.getDesignTitle()+"' ,  designName = '"+de.getDesignName()+"',designGrade='"+de.getDesignGrade()+"' where designId='"+de.getDesignId()+"' ";
			System.out.println(sql);
			return DBmanager.executeUpdate(sql);
		}
	}

